{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling with Spark SQL Quiz\n",
    "\n",
    "This quiz uses the same dataset and most of the same questions from the earlier \"Quiz - Data Wrangling with Data Frames Jupyter Notebook.\" For this quiz, however, use Spark SQL instead of Spark Data Frames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import udf\n",
    "from pyspark.sql.types import StringType\n",
    "from pyspark.sql.types import IntegerType\n",
    "from pyspark.sql.functions import desc\n",
    "from pyspark.sql.functions import asc\n",
    "from pyspark.sql.functions import sum as Fsum\n",
    "\n",
    "import datetime\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "# TODOS: \n",
    "# 1) import any other libraries you might need\n",
    "# 2) instantiate a Spark session \n",
    "# 3) read in the data set located at the path \"data/sparkify_log_small.json\"\n",
    "# 4) create a view to use with your SQL queries\n",
    "# 5) write code to answer the quiz questions \n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"Data wrangling with Spark SQL\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "path = \"data/sparkify_log_small.json\"\n",
    "user_log = spark.read.json(path)\n",
    "user_log.createOrReplaceTempView(\"user_log_table\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 1\n",
    "\n",
    "Which page did user id \"\"(empty string) NOT visit?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+\n",
      "|            page|\n",
      "+----------------+\n",
      "|Submit Downgrade|\n",
      "|       Downgrade|\n",
      "|          Logout|\n",
      "|   Save Settings|\n",
      "|        Settings|\n",
      "|        NextSong|\n",
      "|         Upgrade|\n",
      "|           Error|\n",
      "|  Submit Upgrade|\n",
      "+----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# TODO: write your code to answer question 1\n",
    "spark.sql('''\n",
    "            SELECT DISTINCT page\n",
    "            FROM user_log_table\n",
    "            WHERE page NOT IN (SELECT DISTINCT page\n",
    "                    FROM user_log_table\n",
    "                    WHERE userId = ''\n",
    "                    )\n",
    "        ''').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 2 - Reflect\n",
    "\n",
    "Why might you prefer to use SQL over data frames? Why might you prefer data frames over SQL?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 3\n",
    "\n",
    "How many female users do we have in the data set?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|count|\n",
      "+-----+\n",
      "|  462|\n",
      "+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# TODO: write your code to answer question 3\n",
    "spark.sql('''\n",
    "            SELECT count(DISTINCT userId ) as count\n",
    "            FROM user_log_table\n",
    "            WHERE gender = 'F'\n",
    "        ''').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----+\n",
      "|  artist|count|\n",
      "+--------+-----+\n",
      "|Coldplay|   83|\n",
      "+--------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 4\n",
    "\n",
    "How many songs were played from the most played artist?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----+\n",
      "|  artist|count|\n",
      "+--------+-----+\n",
      "|Coldplay|   83|\n",
      "+--------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# TODO: write your code to answer question 4\n",
    "spark.sql('''\n",
    "            SELECT artist, COUNT(*) AS count\n",
    "            FROM user_log_table\n",
    "            WHERE page = 'NextSong'\n",
    "            GROUP BY artist\n",
    "            ORDER BY count desc\n",
    "            limit 1\n",
    "        ''').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 5 (challenge)\n",
    "\n",
    "How many songs do users listen to on average between visiting our home page? Please round your answer to the closest integer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: write your code to answer question 5\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
